package cnu.ldc.jdbctest;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.time.LocalDateTime;
import java.util.ArrayList;
import java.util.List;

import cnu.ldc.model.User;
import cnu.ldc.util.DBconnection;

public class UsersTableHandle {

	public  List<User> queryAll() {
		String sql="select * from users";
		Connection con=DBconnection.getConnection();
		PreparedStatement pasmt;
		List<User> ls=new ArrayList<>();
		try {
			pasmt = con.prepareStatement(sql);
			ResultSet rs=pasmt.executeQuery();

			while(rs.next()) {
			User u=new User(rs.getInt(1),rs.getString(2),rs.getString(3),rs.getString(4),
					LocalDateTime.now());
			ls.add(u);
				
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	return ls;

	}
	public User queryById(int id) {
		String sql="select*from users where id=?";
	
		User user=null;
		try {
			Connection con=DBconnection.getConnection();
			PreparedStatement pst=con.prepareStatement(sql);
			pst.setInt(1, id);
			ResultSet rs=pst.executeQuery();
			if(rs.next()) {
			user = new User(rs.getInt(1),rs.getString(2),rs.getString(3),rs.getString(4),LocalDateTime.now());
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return user;
	}
	public  int add(User user) {
		int num=0;
		String sql="insert into users(id,name,password,sex) values(users_id.nextval,?,?,?)";
		Connection con=DBconnection.getConnection();
		try {
			PreparedStatement ps=con.prepareStatement(sql, new String[] {"password"});
			ps.setString(1, user.getName());
			ps.setString(2, user.getPassword());
			ps.setString(3, user.getSex());
			num=ps.executeUpdate();
			ResultSet rs=ps.getGeneratedKeys();

			while(rs.next()) {
				System.out.println(rs.getInt(1));
			}
		
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return num;
		
	}
	public int delete(int id) {
		int num=0;
		String sql="delete from users where id=?";
		Connection con=DBconnection.getConnection();
		try {
			PreparedStatement pst=con.prepareStatement(sql);
			pst.setInt(1, id);
			num=pst.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return num;
	}
	public int update(User user) {
		int num=0;
		String sql="update users set name=?,password=?,sex=?where id=?";
		Connection con=DBconnection.getConnection();
		try {
			PreparedStatement pst=con.prepareStatement(sql);
			pst.setString(1,user.getName() );
			pst.setString(2, user.getPassword());
			pst.setString(3,user.getSex() );
			pst.setInt(4,user.getId() );
			num=pst.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		
		return num;
	
	}

}
